CREATE TABLE cmsSytemStatus
(
	SystemStatus nvarchar(128) not null primary key,
	SystemStatusText nvarchar(256),
	Note ntext,
	OrderId int,
	CreatedDate Datetime default(getdate()),
	CreatedById int
)
GO
ALTER TABLE cmsCustomer
ADD IntroductionCustomerId int
GO
ALTER TABLE cmsCustomer
ADD IntroductionCustomerCode nvarchar(128)
GO

SELECT * FROM cmsCustomer
GO

ALTER PROCEDURE [dbo].[spcmsCustomer_GetByPK] 
	(
@Id int
)
AS
SELECT	 
*
FROM 
cmsCustomer  
WHERE 
[Id]=@Id


RETURN
GO
ALTER PROCEDURE [dbo].[spcmsCustomer_GetAll] 
AS
Select 
*
from 
cmsCustomer
Return
GO
GO

CREATE PROC spcms_Customer_Register
(
	@FullName nvarchar(50)=null,
	@UserName nvarchar(50)=null,
	@Password nvarchar(50)=null,
	@Sex bit=null,
	@Brithday datetime=null,
	@Email nvarchar(50)=null,
	@Phone nvarchar(50)=null,
	@Address nvarchar(250)=null,
	@RegisterDay datetime=null,
	@IsEnable bit=null,
	@IsVangLai bit=null,
	@Identification nvarchar(128)=null,
	@CustomerCode nvarchar(128)=null,
	@CreatedDate datetime=null,
	@LastSaveDate datetime=null,
	@CreatedById int=null,
	@LastModifiedById int=null,
	@IntroductionCustomerId int,
	@IntroductionCustomerCode nvarchar(128)
)
AS
BEGIN
	IF EXISTS(SELECT 1 from cmsCustomer WHERE UserName = @UserName)
		RETURN -2;
	ELSE IF EXISTS (SELECT 1 FROM cmsCustomer WHERE Email = @Email)
		RETURN -1;
	ELSE
	BEGIN
		Insert Into 
		cmsCustomer 
		(
		[FullName],
		[UserName],
		[Password],
		[Sex],
		[Brithday],
		[Email],
		[Phone],
		[Address],
		[RegisterDay],
		[IsEnable],
		[IsVangLai],
		[Identification],
		[CustomerCode],
		[CreatedDate],
		[LastSaveDate],
		[CreatedById],
		[LastModifiedById],
		[IntroductionCustomerId],
		[IntroductionCustomerCode]
		) 
		values(
		@FullName,
		@UserName,
		@Password,
		@Sex,
		@Brithday,
		@Email,
		@Phone,
		@Address,
		@RegisterDay,
		@IsEnable,
		@IsVangLai,
		@Identification,
		(SELECT dbo.GenerateCustomerCode()),
		GETDATE(),
		 CASE WHEN @LastSaveDate = '1900-1-1' THEN NULL ELSE @LastSaveDate END,
		@CreatedById,
		@LastModifiedById,
		@IntroductionCustomerId,
	@IntroductionCustomerCode
		)
		RETURN Scope_identity()
	END
END
GO
GO
ALTER PROCEDURE [dbo].[spcmsCustomer_Insert] 
	(
@FullName nvarchar(50)=null,
@UserName nvarchar(50)=null,
@Password nvarchar(50)=null,
@Sex bit=null,
@Brithday datetime=null,
@Email nvarchar(50)=null,
@Phone nvarchar(50)=null,
@Address nvarchar(250)=null,
@RegisterDay datetime=null,
@IsEnable bit=null,
@IsVangLai bit=null,
@Identification nvarchar(128)=null,
@CustomerCode nvarchar(128)=null,
@CreatedDate datetime=null,
@LastSaveDate datetime=null,
@CreatedById int=null,
@LastModifiedById int=null,
	@IntroductionCustomerId int,
	@IntroductionCustomerCode nvarchar(256)
)
AS
Insert Into 
cmsCustomer 
(
[FullName],
[UserName],
[Password],
[Sex],
[Brithday],
[Email],
[Phone],
[Address],
[RegisterDay],
[IsEnable],
[IsVangLai],
[Identification],
[CustomerCode],
[CreatedDate],
[LastSaveDate],
[CreatedById],
[LastModifiedById],
	[IntroductionCustomerId],
		[IntroductionCustomerCode]
) 
values(
@FullName,
@UserName,
@Password,
@Sex,
@Brithday,
@Email,
@Phone,
@Address,
@RegisterDay,
@IsEnable,
@IsVangLai,
@Identification,
(SELECT dbo.GenerateCustomerCode()),
GETDATE(),
 CASE WHEN @LastSaveDate = '1900-1-1' THEN NULL ELSE @LastSaveDate END,
@CreatedById,
@LastModifiedById,
@IntroductionCustomerId,
		@IntroductionCustomerCode
)
RETURN Scope_identity()

GO

ALTER PROCEDURE [dbo].[spcmsCustomer_UpdateByPK] 
	(
@Id int,
@FullName nvarchar(50)=null,
@UserName nvarchar(50)=null,
@Password nvarchar(50)=null,
@Sex bit=null,
@Brithday datetime=null,
@Email nvarchar(50)=null,
@Phone nvarchar(50)=null,
@Address nvarchar(250)=null,
@RegisterDay datetime=null,
@IsEnable bit=null,
@IsVangLai bit=null,
@Identification nvarchar(128)=null,
@CustomerCode nvarchar(128)=null,
@CreatedDate datetime=null,
@LastSaveDate datetime=null,
@CreatedById int=null,
@LastModifiedById int=null,
@IntroductionCustomerId int,
	@IntroductionCustomerCode nvarchar(256)
)
AS
Update  cmsCustomer Set 
[FullName]=@FullName,
[UserName]=@UserName,
[Password]=@Password,
[Sex]=@Sex,
[Brithday]=@Brithday,
[Email]=@Email,
[Phone]=@Phone,
[Address]=@Address,
[RegisterDay]=@RegisterDay,
[IsEnable]=@IsEnable,
[IsVangLai]=@IsVangLai,
[Identification]=@Identification,
[CustomerCode]=@CustomerCode,
[CreatedDate]=@CreatedDate,
[LastSaveDate]=CASE WHEN @LastSaveDate = '1900-1-1' THEN NULL ELSE @LastSaveDate END,
[CreatedById]=@CreatedById,
[LastModifiedById]=@LastModifiedById,
 IntroductionCustomerId = @IntroductionCustomerId,
 IntroductionCustomerCode = @IntroductionCustomerCode
where 
[Id]=@Id


Return

GO
CREATE PROC cmsAccount_GetAccountInfo
@CustomerId int
AS
SELECT TOP 1 *,(SELECT CreatedDate FROM cmsLogAccount where AccountId = cmsAccount.AccountId) AS LastTranDate FROM cmsAccount WHERE CustomerId = @CustomerId
ORDER BY CreatedDate DESC

Go
CREATE PROC cmsAccount_GetAccountInfoByCode
@CustomerCode nvarchar(128)
AS
SELECT TOP 1 *,(SELECT CreatedDate FROM cmsLogAccount where AccountId = cmsAccount.AccountId) AS LastTranDate FROM cmsAccount WHERE CustomerCode = @CustomerCode
ORDER BY CreatedDate DESC

Go
ALTER PROCEDURE [dbo].[spcmsCustomer_GetByUserName]   
 (  
@UserName nvarchar(250) )  
AS  
SELECT    
*
FROM   
cmsCustomer    
WHERE   
[UserName]=@UserName    
RETURN  